import pandas as pd
import sqlite3
from docx import Document
import os
import re
# from pypinyin import pinyin, Style
from datetime import datetime
from PIL import Image  # 用于图片拼接

# 数据库连接
conn = sqlite3.connect('db2.sqlite3')
cursor = conn.cursor()

# 创建题库表，增加create_time和update_time字段，修改subject为subject_id
cursor.execute('''
CREATE TABLE IF NOT EXISTS exam_system_question (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    subject_id INTEGER,
    content TEXT,
    question_type TEXT,
    options TEXT,
    answer TEXT,
    difficulty TEXT,
    image TEXT,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP
)
''')
conn.commit()

# # 获取科目拼音,这个是把汉字转拼音的示例
# def get_subject_pinyin(subject):
#     pinyin_list = pinyin(subject, style=Style.NORMAL)
#     return ''.join([item[0] for item in pinyin_list])

# 获取科目拼音
def get_subject_pinyin(subject_id):
    # 假设科目名称可以通过subject_id从其他表或字典中获取
    # 这里简单返回subject_id的字符串形式
    return str(subject_id)

# 判断题型
def determine_question_type(options, answer):
    if not options[0]:
        return 'judge'
    elif len(answer) == 1:
        return 'single'
    else:
        return 'multiple'

# 从数据库中读取exam_system_question 表中的最大id值
def get_max_id():
    cursor.execute('SELECT MAX(id) FROM exam_system_question')
    return int(cursor.fetchone()[0])

# 横向拼接多张图片
def combine_images_horizontally(image_paths, output_path):
    images = [Image.open(path) for path in image_paths]
    widths, heights = zip(*(img.size for img in images))

    total_width = sum(widths)
    max_height = max(heights)

    combined_image = Image.new('RGB', (total_width, max_height))

    x_offset = 0
    for img in images:
        combined_image.paste(img, (x_offset, 0))
        x_offset += img.size[0]

    combined_image.save(output_path)
    return output_path

# 从Word文件导入题库
def import_from_word(file_path, subject_id):
    doc = Document(file_path)
    print(doc)
    total_imported = 0
    type_counts = {'单选': 0, '多选': 0, '判断': 0}
    subject_pinyin = get_subject_pinyin(subject_id)
    image_folder = './media/question_images'
    # os.makedirs(image_folder, exist_ok=True)  # 创建图片保存文件夹

    current_question = None
    current_options = []
    current_answer = None
    current_image_paths = []  # 存储多张图片路径
    question_number = get_max_id()  # 从数据库中读取exam_system_question 表中的最大id值

    # 遍历Word文档中的每一个段落
    for para in doc.paragraphs:
        text = para.text.strip()

        # 处理图片
        if para.runs and para.runs[0].text == '' and para.runs[0].element.xpath('.//w:drawing'):
            image_name = f'{subject_pinyin}_{question_number}_{len(current_image_paths) + 1}.png'
            image_path = os.path.join(image_folder, image_name)
            for shape in para._element.xpath('.//w:drawing'):
                blip = shape.xpath('.//a:blip/@r:embed')[0]
                image_part = doc.part.related_parts[blip]
                with open(image_path, 'wb') as f:
                    f.write(image_part.blob)
            current_image_paths.append(image_path)
            print(f"当前图片路径：{image_path}")
            continue

        if not text:
            continue

        # 题干行
        if re.match(r'^\.?\d+\s+', text):
            current_question = text
            print(f"当前题干：{current_question}")
            continue



        # 选项行（以A.、B.、C.等开头）
        if re.match(r'^[A-Z]\.', text):
            current_options.append(text)
            # print(f"当前选项：{text}")
            continue

        # 答案行（以“答案：”开头）
        if text.startswith('答案：'):
            current_answer = text.replace('答案：', '').strip()
            # print(f"当前答案：{current_answer}")




        # 题目结束，插入数据库
        if current_question and current_answer:
            options_str = '\n'.join(current_options) if current_options else ''
            question_type = determine_question_type(current_options, current_answer)
            difficulty = 'medium'  # 默认难度
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

            # 处理多张图片
            if len(current_image_paths) > 1:
                combined_image_name = f'{subject_pinyin}_{question_number}_combined.png'
                combined_image_path = os.path.join(image_folder, combined_image_name)
                combine_images_horizontally(current_image_paths, combined_image_path)
                final_image_path = combined_image_path
            elif len(current_image_paths) == 1:
                final_image_path = current_image_paths[0]
            else:
                final_image_path = None

            cursor.execute('''
            INSERT INTO exam_system_question (subject_id, content, question_type, options, answer, difficulty, image, create_time, update_time)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (subject_id, current_question, question_type, options_str, current_answer, difficulty, final_image_path, current_time, current_time))

            question_number += 1
            total_imported += 1
            type_counts[question_type] += 1

        # 重置当前题目信息,无论是否添加成功，都要清空
        current_question = None
        current_options = []
        current_answer = None
        current_image_paths = []

    conn.commit()
    print(f"从Word文件导入完成，共导入{total_imported}题。")
    print(f"题型分布：单选{type_counts['单选']}题，多选{type_counts['多选']}题，判断{type_counts['判断']}题。")

# 从Excel文件导入题库
def import_from_excel(file_path, subject_id):
    df = pd.read_excel(file_path)
    total_imported = 0
    type_counts = {'单选': 0, '多选': 0, '判断': 0}

    for index, row in df.iterrows():
        question = row.get('题干')
        options = row.get('选项', '')
        answer = row.get('答案')
        difficulty = row.get('难度', '中')
        image = row.get('图片', '')

        if not question or not answer:
            continue

        question_type = determine_question_type(options, answer)
        current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        cursor.execute('''
        INSERT INTO exam_system_question (subject_id, question, question_type, options, answer, difficulty, image, create_time, update_time)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (subject_id, question, question_type, options, answer, difficulty, image, current_time, current_time))

        total_imported += 1
        type_counts[question_type] += 1

    conn.commit()
    print(f"从Excel文件导入完成，共导入{total_imported}题。")
    print(f"题型分布：单选{type_counts['单选']}题，多选{type_counts['多选']}题，判断{type_counts['判断']}题。")

# 主程序
if __name__ == '__main__':
    file_type = input("请选择文件类型（1: Excel, 2: Word）: ")
    file_path = input("请输入文件路径: ")
    subject_id = int(input("请输入科目ID（数字）: "))
    if not os.path.exists(file_path):
        print("文件不存在.")
        file_path = input("请输入文件路径: ")
    if file_type == '1':
        import_from_excel(file_path, subject_id)
    elif file_type == '2':
        import_from_word(file_path, subject_id)
    else:
        print("无效的文件类型选择。")

    # 关闭数据库连接
    conn.close()